﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Model;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;

namespace Automobile.service.DAL
{
    public class SupplierDal
    {
        string str = "Data Source=.;Initial Catalog=qichefuwu;Integrated Security=True";

        /// <summary>
        /// 显示库存盘点单信息
        /// </summary>
        /// <returns></returns>
        public List<SupplierModel> Supplier()
        {
            string sql = "select Supplier_Id,Supplier_Name,Supplier_People,Supplier_Phone,Supplier_Addr from Supplier";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, str);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<SupplierModel> list = new List<SupplierModel>();
            foreach (DataRow item in table.Rows)
            {
                SupplierModel s = new SupplierModel();
                s.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                s.Supplier_Name = item["Supplier_Name"].ToString();
                s.Supplier_People = item["Supplier_People"].ToString();
                s.Supplier_Phone = item["Supplier_Phone"].ToString();
                s.Supplier_Addr = item["Supplier_Addr"].ToString();
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 添加库存盘点单信息
        /// </summary>
        /// <returns></returns>
        public int AddSupplier(SupplierModel s)
        {
            string sql = "insert into Supplier(Supplier_Name,Supplier_People,Supplier_Phone,Supplier_Addr) values ('" + s.Supplier_Name + "','" + s.Supplier_People + "','" + s.Supplier_Phone + "','" + s.Supplier_Addr + "')";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        /// <summary>
        /// 删除库存盘点单信息
        /// </summary>
        /// <returns></returns>
        public int DeleSupplier(int Supplier_Id)
        {
            string sql = "delete from Supplier where Supplier_Id=" + Supplier_Id;
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }

        /// <summary>
        /// 取单个库存盘点单信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetSupplier(string Supplier_Id)
        {
            string sql = "select Supplier_Id,Supplier_Name,Supplier_People,Supplier_Phone,Supplier_Addr from Supplier where Supplier_Id=" + Supplier_Id;

            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            DataTable table = new DataTable();
            SqlDataAdapter ada = new SqlDataAdapter(comm);
            ada.Fill(table);
            conn.Close();
            return table;
        }

        /// <summary>
        /// 更新库存盘点单信息
        /// </summary>
        /// <param name="stu"></param>
        /// <returns></returns>
        public int UpdateSupplierModel(SupplierModel s)
        {
            string sql = "update Supplier set Supplier_Name='" + s.Supplier_Name + "',Supplier_People='" + s.Supplier_People + "',Supplier_Phone='" + s.Supplier_Phone + "',Supplier_Addr='" + s.Supplier_Addr + "' where Supplier_Id=" + s.Supplier_Id;
            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            //执行命令
            int count = comm.ExecuteNonQuery();
            conn.Close();

            return count;
        }
       
    }
}
